Correlated Subqueries

Some queries require that the output be limited by the results of a subquery. For example, a table may contain a field that includes an effective date and you may want your query to limit the results to the record that contains the latest effective date.

In this example, the “Employee_Emerg_Contact” table from the sample database will be used. This table contains emergency contact information for each employee and is updated whenever contact information changes. The table contains items such as the employee ID number, first and last name, phone number, and effective date. The “effective_date” field contains the date that the record was updated.

The example will find the latest contact information for “Elmer Johnson”, for which multiple records exist containing various effective dates in the database. Elmer Johnson is an emergency contact of employee 018 (Priscilla Johnson) in the Employees Table.

Creating the Main Query

Create the main query using the Employee_Emerg_Contact table. Add lastname, firstname, phone, and effective_date to the SELECT clause. For the WHERE clause, filter by lastname = 'johnson' and firstname = 'elmer'.

Note that this query can return multiple records for that individual.

This shows the Main Query that was created within the Build Query dialog box.

This query shown below will retrieve three records for Elmer Johnson with effective dates of 1/1/2000, 1/1/2001, and 1/1/2002, each with a different phone number.

A subquery is needed (shown in the figure below) to return the record with the latest effective date.

SELECT Max( Employee_Emerg_Contact1.effective_date ) AS Max_effective_date
FROM Employee_Emerg_Contact Employee_Emerg_Contact1
WHERE Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id

Notice that the subquery is “linked” to the main query in the very last line. As each row from the main query is examined, it will be compared against the results of the subquery. These types of subqueries are called “correlated”, as the results of the main query are dependent on the results of the subquery. Also notice that in this example, the main query and the subquery are using the same table. Whenever a table is used multiple times in the same query, it is important to add a table alias for each instance of the table (Argos adds the table aliases automatically). Although both queries use the Employee_Emerg_Contact table, in one case the alias is Employee_Emerg_Contact and in the other it is Employee_Emerg_Contact1. The database considers these as separate tables and does not get confused as long each field is prefaced with the table alias.

Recall that the subquery is to provide a single value for Employee_Emerg_contact.effective_date in the WHERE clause. That is, a multiple lines of SQL are used to provide a value to be used in the WHERE clause.

Creating the Subquery

The steps for creating the subquery shown above will now be discussed. Within the Build Query dialog box, click the Add Query button. The Query dialog box will be displayed again such that you can use it to create the subquery. Click the Show Tables button and select the Employee_Emerg_Contact table. Double-click the effective_date field which moves the field under the SELECT tab as shown below.

Notice that in the subquery, the table name is called Employee_Emerg_Contact1 because the main query and subquery are using the same table (as discussed above).

This shows how the table for the sub query was selected.

Click the “Summing” icon and select “Max” since the subquery is to find the latest effective date.

This shows the use of the Summing field to specify the latest effective date.

Next create a WHERE statement to link the main query to the subquery using the employee ID as the common field. When using the editor to create the WHERE statement, since the emp_id field exists in both Employee_Emerg_Contact and Employee_Emerg_Contact1 tables, thus both must be referenced.

Select the Employee_Emerg_Contact1 table as shown below.

This shows the use of the WHERE tab within the sub query.

Click the ellipses button inside the Condition field to launch the SQL Editor.
This image shows the SQL Editor before data is entered.

Click the insert field icon to display a list of database fields. Select Employee_Emerg_contact.emp_id. This now links the emp_id between the main query and the subquery.
The Pick a Field dialog box where the field to link the main query to the sub query is chosen.

The subquery now appears within the Build Query dialog box as shown below.
This shows the sub query within the Build Query dialog box.

The subquery is shown below.

select Max( Employee_Emerg_Contact1.effective_date ) as Max_effective_date
from Employee_Emerg_Contact Employee_Emerg_Contact1
where Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id

Placing the Subquery in the Main Query

Now that the main query and subquery have been built, the subquery needs to be placed into the appropriate section of the main query. To perform this linkage, click the “Add this Subquery to the Conditional Fields Tree” button within the Subquery window.

This shows how to position the sub query into the main query. This is done wiithin the Build Query dialog box.

Recall that for this individual, three records with different effective dates exist in the database. The query/subquery obtained the latest.

The Conditional Fields (WHERE) tab is displayed at the bottom of the window where you can use the appropriate aggregate function to link the subquery to the main query. In this case choose the = operator since we want the record that has an effective date equal to the effective date returned by the subquery. Make sure to choose the effective_date field from the Employee_Emerg_Contact table in the Main Query.

The aggregate function linking the sub query to the main query. Showing the where tab. The subquery is listed at the top of the tab, the equals operator is selected from among the radio buttons. The query field has emergency contact 1 effective date, the table is employee emerg contact, and the field is effective date.

The process is now complete which creates the complete SQL shown below.

SELECT Employee_Emerg_Contact.lastname,
Employee_Emerg_Contact.firstname,
Employee_Emerg_Contact.phone,
Employee_Emerg_Contact.effective_date
FROM Employee_Emerg_Contact where Employee_Emerg_Contact.lastname ='johnson'
AND Employee_Emerg_Contact.firstname ='elmer' AND Employee_Emerg_Contact.effective_date = ( select Max( Employee_Emerg_Contact1.effective_date )
AS Max_effective_date
FROM Employee_Emerg_Contact Employee_Emerg_Contact1 WHERE Employee_Emerg_Contact1.emp_id = Employee_Emerg_Contact.emp_id )

Results

Executing the query yields the results shown below in which the latest effective date for Elmer Johnson is provided.

This image shows the query results containing the contact's name (elmer johnson), his phone number, and January 1, 2002 as the latest effective date.